#!/usr/bin/env python
# -*- coding: utf-8 -*-

__author__ = 'James Iter'
__date__ = '15/12/25'
__contact__ = 'james.iter.cn@gmail.com'
__copyright__ = '(c) 2015 by James Iter.'


import mysql.connector
from mysql.connector import errorcode
from models import Utils
import random
import json
import datetime
import time

import jimit as ji

ts = ji.Common.ts()


def insert_rows(_cnx=None, _name=None, _rows=None):
    if _cnx is None or _name is None or _rows is None:
        raise ValueError('_cnx, _name and _rows must not None')

    if _rows.__len__() > 0:
        _columns = _rows[0].keys()
        _insert_keys = ', '.join(_columns)
        _insert_values = ')s, %('.join(_columns)
        # 转存数据插入语句
        _sql_stmt = (''.join(['INSERT INTO ', _name, '(', _insert_keys, ') VALUES (%(', _insert_values, ')s)']))
        _cursor = _cnx.cursor()
        try:
            for _row in _rows:
                _cursor.execute(_sql_stmt, _row)
            _cnx.commit()
        except mysql.connector.Error as err:
            ret = dict()
            ret['state'] = ji.Common.exchange_state(50050)
            ret['state']['sub']['zh-cn'] = ''.join([ret['state']['sub']['zh-cn'], ': ', err._full_msg])
            raise ji.PreviewingError(json.dumps(ret))
        finally:
            _cursor.close()
            _cnx.close()

test_db = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'dbpswd',
    'port': 3306,
    'database': 'sparrow'
}

light_sql = [
'DROP DATABASE IF EXISTS sparrow;',
'CREATE DATABASE IF NOT EXISTS sparrow;',
'sparrow',
'DROP TABLE IF EXISTS login_auth;',
"""CREATE TABLE IF NOT EXISTS login_auth(
    id BIGINT UNSIGNED NOT NULL,
    login_name VARCHAR(30) NOT NULL,
    password VARCHAR(100) NOT NULL,
    create_time BIGINT NOT NULL,
    PRIMARY KEY (id))
    ENGINE=InnoDB;""",
'DROP TABLE IF EXISTS user_info;',
"""CREATE TABLE IF NOT EXISTS user_info(
    id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    nick_name VARCHAR(30) NOT NULL,
    salary BIGINT NOT NULL,
    PRIMARY KEY (id))
    ENGINE=InnoDB;""",
'ALTER TABLE login_auth ADD INDEX (login_name);',
'ALTER TABLE login_auth ADD INDEX (create_time);',
'ALTER TABLE user_info ADD INDEX first_name__last_name (first_name, last_name);'
]


def exec_sql(_cnx=None, _sql_stmt=None):
    _cursor = _cnx.cursor()
    try:
        _cursor.execute(_sql_stmt)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            e_msg = 'Something is wrong with your user name or password'
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            e_msg = 'Database does not exist'
        else:
            e_msg = err.msg
        print(e_msg)
        exit(err.errno)
    finally:
        _cursor.close()


def init_db(_sql_stmt=None):
    _cnxpool = mysql.connector.pooling.MySQLConnectionPool(
        host=test_db['host'],
        user=test_db['user'],
        password=test_db['password'],
        port=test_db['port'],
        pool_size=32
    )

    _cnx = _cnxpool.get_connection()
    exec_sql(_cnx=_cnx, _sql_stmt=_sql_stmt[0])
    exec_sql(_cnx=_cnx, _sql_stmt=_sql_stmt[1])
    try:
        _cnxpool.set_config(database=_sql_stmt[2])
        _cnx = _cnxpool.get_connection()
    except Exception as e:
        print e
    for _sub_sql_stmt in _sql_stmt[3:]:
        exec_sql(_cnx=_cnx, _sql_stmt=_sub_sql_stmt)
    _cnx.close()

init_db(_sql_stmt=light_sql)


def init_hot_cold_db(_db_name=None):
    _sql_stmt = [
        'DROP DATABASE IF EXISTS ' + _db_name + ';',
        'CREATE DATABASE IF NOT EXISTS ' + _db_name + ';',
        _db_name,
        """CREATE TABLE IF NOT EXISTS order_form(
            id BIGINT UNSIGNED NOT NULL,
            create_time BIGINT NOT NULL,
            finished_time BIGINT NOT NULL,
            PRIMARY KEY (id))
            ENGINE=InnoDB;""",
        'ALTER TABLE order_form ADD INDEX (create_time);'
    ]

    init_db(_sql_stmt=_sql_stmt)


def init_hot_cold_db_data(_db_name=None, _start_at=1, _length=1000):
    _cnxpool = mysql.connector.pooling.MySQLConnectionPool(
        host=test_db['host'],
        user=test_db['user'],
        password=test_db['password'],
        port=test_db['port'],
        database=_db_name,
        pool_size=32
    )

    _sql_stmt = ('INSERT INTO order_form (id, create_time, finished_time) '
                 'VALUES (%(id)s, %(create_time)s, %(finished_time)s)')
    _cnx = _cnxpool.get_connection()
    _cursor = _cnx.cursor(dictionary=False, buffered=False)
    try:
        year_month = _db_name.split('__')[1:]
        end_ts = Utils.get_the_cycle_begin_ts(cycle_unit='w', offset=1)
        if year_month[0] == 'hot':
            start_ts = end_ts
        else:
            start_ts = time.mktime(datetime.date.today().replace(year=int(year_month[0]), month=int(year_month[1]),
                                                                 day=1).timetuple())

        _counter = 0
        for _i in range(_start_at, _start_at + _length):

            _row = {
                'id': _i,
                'create_time': start_ts,
                'finished_time': start_ts + 10
            }
            # 半小时间隔
            # 按最小月28天算,总共24*2*28=1344
            start_ts += 1800
            if year_month[0] == 'hot':
                if start_ts >= ts:
                    break
            else:
                if start_ts >= end_ts:
                    break

            _counter += 1
            _cursor.execute(_sql_stmt, _row)

        _cnx.commit()
        return _counter
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            e_msg = 'Something is wrong with your user name or password'
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            e_msg = 'Database does not exist'
        else:
            e_msg = err.msg
        print(e_msg)
        exit(err.errno)
    finally:
        _cursor.close()
        _cnx.close()


init_hot_cold_db(test_db['database'] + '__hot')

print ji.JITime.now_time()

start_at = 1
length = 1000
for offset in range(19, -1, -1):
    month_begin = datetime.date.today()
    offset_year = offset / 12
    offset_month = offset % 12
    the_date = month_begin.replace(year=month_begin.year - offset_year, month=month_begin.month - offset_month, day=1)
    db_name = '__'.join([test_db['database'], str(the_date.year), str(the_date.month)])
    init_hot_cold_db(_db_name=db_name)
    counter = init_hot_cold_db_data(_db_name=db_name, _start_at=start_at, _length=length)
    start_at += counter

init_hot_cold_db_data(_db_name=test_db['database'] + '__hot', _start_at=start_at, _length=length)


print ji.JITime.now_time()

cnxpool = mysql.connector.pooling.MySQLConnectionPool(
    host=test_db['host'],
    user=test_db['user'],
    password=test_db['password'],
    port=test_db['port'],
    database=test_db['database'],
    pool_size=32
)
cnx = cnxpool.get_connection()
cursor = cnx.cursor(dictionary=False, buffered=False)

for i in range(1, 100):
    sql_stmt = ('INSERT INTO login_auth (id, login_name, password, create_time) '
                'VALUES (%(id)s, %(login_name)s, %(password)s, %(create_time)s)')
    sql_stmt_data = {
        'id': i,
        'login_name': ji.Common.generate_random_code(20),
        'password': ji.Common.generate_random_code(10),
        'create_time': random.randint(1420041600, 1451577600)
    }
    cursor.execute(sql_stmt, sql_stmt_data)

cnx.commit()
print ji.JITime.now_time()
